package com.lyc.dao.impl;

import com.lyc.bean.Courier;
import com.lyc.bean.Express;
import com.lyc.dao.BaseCourierDao;
import com.lyc.exception.DuplicateCodeException;
import com.lyc.util.DruidUtil;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class BaseCourierDaoImpl implements BaseCourierDao {

    // 用于查询数据库中的快递员人数
    public static final String SQL_CONSOLE = "SELECT COUNT(ID) data_size1, COUNT(TO_DAYS(REGISTERTIME)=TO_DAYS(NOW()) OR NULL) data_day1 FROM COURIER";
    // 用于查询数据库中的所有快递员信息
    public static final String SQL_FIND_ALL = "SELECT * FROM COURIER";
    // 用于分页查询数据库中的快递员信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM COURIER LIMIT ?,?";
    // 通过快递员手机号查询快递员信息
    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM COURIER WHERE USERPHONE=?";
    // 录入快递员
    public static final String SQL_INSERT = "INSERT INTO COURIER (USERNAME,USERPHONE,IDNUMBER,PASSWORD,EXPRESSNUMBER,REGISTERTIME) VALUES(?,?,?,?,?,NOW())";
    // 快递员修改
    public static final String SQL_UPDATE = "UPDATE COURIER SET USERNAME=?,IDNUMBER=?,PASSWORD=? WHERE USERPHONE=?";
    // 快递的删除
    public static final String SQL_DELETE = "DELETE FROM COURIER WHERE USERPHONE=?";

    /**
     * 用于查询数据库中快递员的个数
     * @return list.size()
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        // 1.获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        // 2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            // 3.填充参数（可选）
            // 4.执行SQL语句
            result = state.executeQuery();
            // 5.获取执行的结果
            if (result.next()){
                int data_size1 = result.getInt("data_size1");
                int data_day1 = result.getInt("data_day1");
                Map data1 = new HashMap();
                data1.put("data_size1", data_size1);
                data1.put("data_day1", data_day1);
                data.add(data1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            // 6.资源的释放
            DruidUtil.close(conn, state, result);
        }
        return data;
    }
    /**
     * 用于查询所有快递员
     * @param limit      是否分页的标记，true表示分页，false表示查询所有快递员
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递员的集合
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        // 1.获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        // 2.预编译SQL语句
        try {
            if (limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                // 3.填充参数（可选）
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            // 4.执行SQL语句
            result = state.executeQuery();
            // 5.获取执行的结果
            while (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String idNumber = result.getString("idNumber");
                String password = result.getString("password");
                String expressNumber = result.getString("expressNumber");
                Timestamp registerTime = result.getTimestamp("registerTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                Courier c = new Courier(id, username, userPhone, idNumber, password, expressNumber, registerTime, loginTime);
                data.add(c);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            // 6.资源的释放
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    /**
     * 根据手机号码查询快递员信息
     * @param userPhone 手机号码
     * @return 查询的快递员信息，手机号不存在时返回null
     */
    @Override
    public Courier findByUserPhone(String userPhone) {
        // 1.获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        // 2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            // 3.填充参数(可选)
            state.setString(1, userPhone);
            // 4.执行SQL语句
            result = state.executeQuery();
            // 5.获取执行的结果
            while (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String idNumber = result.getString("idNumber");
                String password = result.getString("password");
                String expressNumber = result.getString("expressNumber");
                Timestamp registerTime = result.getTimestamp("registerTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                Courier c = new Courier(id, username, userPhone, idNumber, password, expressNumber, registerTime, loginTime);
                return c;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            // 6.资源的释放
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 快递员的录入
     * @param c 要录入的快递员对象
     * @return 录入的结果，true表示录入成功，false表示录入失败
     */
    @Override
    public boolean insert(Courier c){
        // 1.连接的获取
        Connection conn = DruidUtil.getConnection();
        // 2.预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            // 3.填充参数
            state.setString(1, c.getUsername());
            state.setString(2, c.getUserPhone());
            state.setString(3, c.getIdNumber());
            state.setString(4, c.getPassword());
            state.setString(5, c.getExpressNumber());
            // 4.执行SQL语句,并获取执行结果
            return state.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 5.释放资源
            DruidUtil.close(conn, state, null);
        }
        return false;
    }

    /**
     * 快递员的修改
     * @param userPhone  要修改的快递员手机号
     * @param newCourier 新修改的快递员对象(username, userPhone, idNumber, password)
     * @return 修改的结果，true表示录入成功，false表示录入失败
     */
    @Override
    public boolean update(String userPhone, Courier newCourier) {
        // 1.连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        // 2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1, newCourier.getUsername());
            state.setString(2, newCourier.getIdNumber());
            state.setString(3, newCourier.getPassword());
            state.setString(4, userPhone);
            return state.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn, state,null);
        }
        return false;
    }

    /**
     * 根据手机号删除单个快递员信息
     * @param userPhone 要删除的快递员手机号
     * @return 删除的结果，true表示删除成功，false表示删除失败
     */
    @Override
    public boolean delete(String userPhone) {
        // 1.连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        // 2.预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setString(1, userPhone);
            return state.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn, state,null);
        }
        return false;
    }
}
